/**
 * Module dependencies.
 */

var express = require('express');
var routes = require('./routes');
var user = require('./routes/user');
var http = require('http');
var path = require('path');
var mysql = require('mysql');

//数据库链接信息.创建数据库连接池。
var pool = mysql.createPool({
	host : 'localhost',
	port : '3306', //可以不填写，默认为3306
	user : 'root',
	password : 'root',
	database : 'quickstart'
});

var app = express();

// all environments
app.set('port', process.env.PORT || 3000);
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.bodyParser());
app.use(express.methodOverride());
app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));

// development only
if ('development' == app.get('env')) {
	app.use(express.errorHandler());
}

//app.get('/', routes.index);
app.get('/users', user.list);

//链接数据库测试 start

//显示数据
app.get('/', function(req, res) {
	pool.getConnection(function(err, connection) {
		connection.query('SELECT * from ss_user ', function(err, rows, fields) {
			if (err)
				throw err;
			//res.send('The solution is: ', rows[0]);
			//res.send('user id is:'+req.params.id);
			res.render('user.jade', {
				title : 'User List',
				user : rows
			});
			//console.log('The solution is: ', rows);
		});
		connection.release();
	});
});
//新增测试
app.get('/create', function(req, res) {
	res.render('create.jade', {
		title : 'Create a new user'
	});
});

app.post('/create', function(req, res) {
	pool.getConnection(function(err, connection) {
		connection.query('insert into ss_user set ?', {
			id : req.body.user.id,
			login_name : req.body.user.login_name,
			name : req.body.user.name,
			password : req.body.user.password
		}, function(err, fields) {
			if (err)
				throw err;
			//console.log('Insert is success.');
		});
		//新增之后查询操作
		connection.query('SELECT * from ss_user', function(err, rows, fields) {
			if (err)
				throw err;
			res.render('user.jade', {
				title : 'User List',
				user : rows
			});
			//console.log('The solution is: ', rows[0]);
		});
		connection.release();
	});
});

//修改操作。修改指定id的数据
app.get('/update/:id', function(req, res) {
	pool.getConnection(function(err, connection) {
		connection.query('SELECT * from ss_user where id=?',[req.params.id],function(err, rows, fields) {
			if (err)
				throw err;
			console.log('search is success.');
			res.render('create.jade', {
				title : 'Update user',
				user : rows[0]
			});
		});
		connection.release();
	});
});
app.post('/update', function(req, res) {
	pool.getConnection(function(err, connection) {
		connection.query('update ss_user set ? where id = ?', [{
			id : req.body.user.id,
			login_name : req.body.user.login_name,
			name : req.body.user.name,
			password : req.body.user.password
		},req.body.user.id], function(err, fields) {
			if (err)
				throw err;
			//console.log('Insert is success.');
		});
		//新增之后查询操作
		connection.query('SELECT * from ss_user', function(err, rows, fields) {
			if (err)
				throw err;
			res.render('user.jade', {
				title : 'User List',
				user : rows
			});
			//console.log('The solution is: ', rows[0]);
		});
		connection.release();
	});
});

//修改操作。修改指定id的数据
app.get('/delete/:id', function(req, res) {
	pool.getConnection(function(err, connection) {
		connection.query('delete from  ss_user  where id = ?', [req.params.id], function(err, fields) {
			if (err)
				throw err;
		});
		//删除之后查询操作
		connection.query('SELECT * from ss_user', function(err, rows, fields) {
			if (err)
				throw err;
			res.render('user.jade', {
				title : 'User List',
				user : rows
			});
		});
		connection.release();
	});
});
//链接数据库测试 end

http.createServer(app).listen(app.get('port'), function() {
	console.log('Express server listening on port ' + app.get('port'));
});
